package com.iris.live.services.data.repositories;

import com.iris.live.services.data.models.Lsh库存表Model;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * Created by EngLee on 2016/8/14
 */
@Transactional
public interface Lsh库存表Repository extends JpaRepository<Lsh库存表Model, Integer>, JpaSpecificationExecutor<Lsh库存表Model> {

    @Query("select u from Lsh库存表Model u where u.生产号 = ?1")
    Lsh库存表Model findLshRecordByProId(String proId);

    @Modifying
    @Query("update Lsh库存表Model a set a.车辆零售状态='未卖',a.报价编号=null where a.库存编号=?1")
    int updateLshStoreStatusById(Integer id);

    @Modifying
    @Query("update Lsh库存表Model a set a.车架号=?2,a.生产号=?2 where a.库存编号=?1")
    int updateLshStoreVinById(Integer id, String vin);

//    @Modifying
//    @Query("update Lsh库存表Model  a set a.申请销售顾问 = ?2, a.调车理由 = ?3, a.协商调车款项金额 = ?4 where a.车架号 = ?1")
//    int updateByVIN(String VIN, String guest, String reason, Integer amount);

//    @Modifying
//    @Query("update Lsh库存表Model a set "
//    		+ "a.车辆物流状态 = ?2,车型编号=?3,生产日期=?4,车架号=?5,"
//    		+ "欧版底盘号=?6,发动机号=?7,VSB号=?8,颜色代码=?9,颜色=?10,"
//    		+ "内饰代码=?11,内饰=?12,原厂配置=?13,加配配置=?14,加配价格含税=?15,"
//    		+ "采购价格含税=?16,采购价格未税=?17,实际MSRP含税=?18,厂家确认订单日期=?19,标配MSRP含税=?20,"
//    		+ "预计下线日期=?21,批发商=?22 where a.生产号 = ?1")
//    int updateByProductNo(String productNo,String status,Integer p3,Date p4,String VIN,
//    		String p6,String p7,String p8,String p9,String p10,
//    		String p11,String p12,String p13,String p14,Integer p15,
//    		Integer p16,Integer p17,Integer p18,Date p19,Integer p20,
//    		Date p21,String p22);

//    @Modifying
//    @Query("update Lsh库存表Model set 申请销售顾问 = ?2, 调车理由 = ?3,协商调车款项金额 = ?4 WHERE	库存编号 = ?1")
//    int updateByStockNo(Integer stockNo, String guest, String reason, Integer amount);

    @Modifying
    @Query("UPDATE Lsh库存表Model SET 申请销售顾问 = ?2, 调车理由 = ?3, 协商调车款项金额 = ?4, 调入经销商 = ?5 " +
            ", 调出经销商 = ?6, 调车物流状态 = ?7, 入库日期=?8 WHERE 库存编号 = ?1 " +
            "and (调车物流状态 is null or 调车物流状态 = '' or (调车财务状态 = '调车款项到账' and 调车物流状态 = '调入入库'))")
    int initStockInTransfer(Integer stockId, String sc, String reason, BigDecimal amount, String innerDealer, String outerDealer, String outerStatus, Date dt);

    @Modifying
    @Query("UPDATE Lsh库存表Model SET 申请销售顾问 = ?2 ,调车理由 = ?3 ,协商调车款项金额 = ?4 ,  调车物流状态 = ?5,入库日期=?6 WHERE 库存编号 = ?1")
    int updateOuterStatusRuku(Integer productNo, String guest, String reason, BigDecimal amount, String outerStatus, Date dt);

    @Modifying
    @Query("UPDATE Lsh库存表Model SET 外部可见级别 = ?1 where 生产号 = ?2")
    int updateOuterVisableLevel(String visableLevel, String productNo);

    @Query("select a from Lsh库存表Model a where a.所属经销商=?1 and (a.nbi车辆状态 <> '现货库存' or a.nbi车辆状态 is null) and a.车架号 not like 'IRISTechno%' ")
    List<Lsh库存表Model> getCarsNotPresent(String permit);

    @Query("select a from Lsh库存表Model a where a.所属经销商=?1 and a.外部可见级别=?2 and a.车辆零售状态='未卖' ")
    List<Lsh库存表Model> getCarsByPermit1(String permit, String level);

    @Query("select a from Lsh库存表Model a where a.所属经销商=?1 and (a.外部可见级别=?2 or a.外部可见级别=?3)")
    List<Lsh库存表Model> getCarsByPermit2(String permit, String level, String level2);

    @Query("select a from Lsh库存表Model a where a.所属经销商=?1 and (a.外部可见级别=?2 or a.外部可见级别=?3 or a.外部可见级别=?4)")
    List<Lsh库存表Model> getCarsByPermit3(String permit, String level, String level2, String level3);

    @Query("select a from Lsh库存表Model a where a.所属经销商=?1 and a.外部可见级别 <> '外部不可见'")
    List<Lsh库存表Model> getCarsByPermit4(String permit);

    @Modifying
    @Query("update Lsh库存表Model a set a.调车物流状态=?2,a.调入外部申请日=?3 where a.库存编号=?1")
    int updateLogisticsStatusById(Integer id, String status, Date dt);

    @Modifying
    @Query("update Lsh库存表Model a set a.调车物流状态=?2,a.调车财务状态=?4,a.调出确认日期=?3 where a.库存编号=?1")
    int updateLshStoreStaById(Integer id, String status, Date dt, String sta);

    @Query("select u from Lsh库存表Model u where u.车架号 = ?1")
    Lsh库存表Model findLshRecordByVin(String proId);

    @Modifying
    @Query("update Lsh库存表Model a set a.调车物流状态=?2,a.所属经销商=?1,a.入库日期=null where a.库存编号=?3")
    int updateStatusAndPermitById(String permit, String status, Integer id);

    @Modifying
    @Query("update Lsh库存表Model a set a.调车财务状态=?2 where a.库存编号=?1")
    int updateCallInDetail(Integer scode, String status);

    @Modifying
    @Query("update Lsh库存表Model a set a.carState=?2 where a.车架号=?1")
    int updateStockStatus(String vin, String status);

    @Modifying
    @Query("update Lsh库存表Model a set a.报价编号=?2,a.车辆零售状态='已卖' where a.库存编号=?1")
    int updateQuotedPriceCodeOrVinById(int id, int qu);

    @Query("select u from Lsh库存表Model u where u.库存编号 = ?1")
    Lsh库存表Model findLshStockByStockId(Integer stockId);

    @Query("select a from Lsh库存表Model a where a.生产号 is not null  and  a.生产号 IN ?1")
    List<Lsh库存表Model> getProdModelList(List<String> condition);

    @Query("select u from Lsh库存表Model u where u.库存编号 in ?1")
    List<Lsh库存表Model> findStockListByStockId(List<Integer> stockId);

    @Query("select a from Lsh库存表Model a where     a.所属经销商=?1  and  a.批发日期  is  null   and  a.vsb财务状态='O'  and a.车架号 not like 'IRISTechno%' ")
    List<Lsh库存表Model> getStockModelList(String permit);

    /**
     * 根据车架号查找车
     * @param vin
     * @return
     */
    @Query("select a from Lsh库存表Model a where a.车架号 = ?1")
    Lsh库存表Model selectByVin(String vin);

}

